package com.zql.Main;

import java.io.BufferedInputStream;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.PrintStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Properties;

/** 
* @author 钟琴隆 E-mail: ai31354907@163.com
* @version 创建时间：2018年7月19日 下午5:19:48 
* 类说明 
* 1.创建数据库，更新数据库配置文件信息
*/
public class GongJu {
	private static final String FILE_PATH="conn//config.properties";
	private static PreparedStatement ps;
	private static ResultSet rs;
	private static String url ;
	private static String username ;
	private static String password ;
	public static Connection conn ;
	public static Statement stmt ;
	public static String XMname = "";
	public static String Daoruchuname = "";
	private static  Date d = new Date();
	private static  SimpleDateFormat sim = new SimpleDateFormat("yyyyMM");
	public static String timed = sim.format(d);
	public static String moshitime = "\""+GongJu.timed+"\"."; 
	public static int jbgn;
	public static String dclj;
	public static void getshujuku() {
		try {
        	Properties p = new Properties();
        	File file = new File(FILE_PATH);
        	FileInputStream fis = new FileInputStream(file);
        	BufferedInputStream bis = new BufferedInputStream(fis);
            p.load(bis);
            //重置配置文件
            p.setProperty("url", String.valueOf("jdbc:postgresql://127.0.0.1:5432/"));
            FileOutputStream fos0 = new FileOutputStream(file);
            p.store(fos0, null);
			//创建数据库
			//Date d = new Date();
			//SimpleDateFormat sim = new SimpleDateFormat("yyyyMM");
			//String datename = XMname+sim.format(d);
			//String datename = XMname;
			String sql ="select * from pg_database where datname='"+XMname+"'; ";
			getDao();
			rs = stmt.executeQuery(sql);
			if(!rs.next()) {
				String jshujuku = "CREATE DATABASE "+XMname+" WITH OWNER = postgres ENCODING = 'UTF8'; ";
				System.out.println(jshujuku);
				ps = conn.prepareStatement(jshujuku);
				ps.executeUpdate();
				System.out.println("数据库创建成功！");
			}else {
				System.out.println("数据库已存在！");
			}
            //准确路径写入配置文件
            p.setProperty("url", String.valueOf("jdbc:postgresql://127.0.0.1:5432/"+XMname));
            FileOutputStream fos = new FileOutputStream(file);
            p.store(fos, null);
            fos.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}finally {
			try {
				if(rs !=null){
					rs.close();
				}
				if(ps !=null){
					ps.close();
				}
				if(stmt != null) {
					stmt.close();
				}
				if(conn != null){
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
	}
	@SuppressWarnings("resource")
	public static void getjianbiao() {
		//0新建表 1复制表及数据 2复制表结构
		try {
			if(jbgn==0) {
			StringBuffer buffer = new StringBuffer();
			BufferedReader bf= new BufferedReader(new FileReader("conn//sql.txt"));
			String s = null;
			buffer.append("CREATE SCHEMA \""+timed+"\";"+"\n");
			while((s = bf.readLine())!=null){//使用readLine方法，一次读一行
				//buffer.append(s.trim()+"\n");
				if(s.trim().contains("CREATE")||s.trim().contains("create")) {
					String ns=s.trim().replace(" \"", " \""+timed+"\".\"");
					buffer.append(ns+"\n");
				}else {
					buffer.append(s.trim()+"\n");
				}
			}
			String jbsql = buffer.toString();
			getDao();
			ps = conn.prepareStatement(jbsql);
			ps.executeUpdate();
			System.out.println("建表完成");
			}else {
				int s= Integer.parseInt(timed);
				//System.out.println(s-1);
				GongJu.getDao();
				String sjkname = "select relname from pg_stat_user_tables where schemaname='"+(s-1)+"'";
				rs = GongJu.stmt.executeQuery(sjkname);
				String sql = "CREATE SCHEMA \""+timed+"\";";
				String tt="";
				while(rs.next()) {
					if(jbgn==1) {
					sql += "CREATE TABLE \""+s+"\".\""+rs.getString(1)+"\" AS SELECT * FROM \""+(s-1)+"\".\""+rs.getString(1)+"\";";
					tt = "表结构及数据";
					}else if(jbgn==2){
					sql += "CREATE TABLE \""+s+"\".\""+rs.getString(1)+"\" AS SELECT * FROM \""+(s-1)+"\".\""+rs.getString(1)+"\" limit 0;";
					tt = "表结构";
					}
				}
				ps = GongJu.conn.prepareStatement(sql);
				ps.executeUpdate();
				System.out.println("复制"+tt+"完成！");
			}
		} catch (IOException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				if(ps !=null){
					ps.close();
				}
				if(stmt != null) {
					stmt.close();
				}
				if(conn != null){
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	
	}
	public static void getImport() {
		try {
			File f1 = new File("");
			List<String> shuju2 = new ArrayList<>();
			int s= Integer.parseInt(timed);
			String f2 = f1.getCanonicalPath()+"/"+Daoruchuname+(s-1);
		    File f = new File(f2);
		    if (!f.exists()) {
		      System.out.println(f2 + " 路径不存在");
		      return;
		    }
		    File fa[] = f.listFiles();
		    for (int i = 0; i < fa.length; i++) {
		      File fs = fa[i];
		      if (fs.isDirectory()) {
		      } else {
		    	 // if(fs.getName().contains("csv")) {
		    	  if(fs.getName().contains("csv")&&!fs.getName().equals("public_praise.csv")&&!fs.getName().equals("model_px.csv")) {
		    		  shuju2.add(fs.getName());
		    	  }else {}
		      }
		    }
			String drsql="";
			for(int i=0;i<shuju2.size();i++) {
				drsql+= "COPY \""+shuju2.get(i).replace(".csv", "")+"\" FROM '"+f2+"/"+shuju2.get(i)+"' WITH csv HEADER;";
			}
			getDao();
			ps = conn.prepareStatement(drsql);
			ps.executeUpdate();
			System.out.println("导入完成！");
		} catch (IOException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				if(ps !=null){
					ps.close();
				}
				if(stmt != null) {
					stmt.close();
				}
				if(conn != null){
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	public static void getDao() throws  SQLException {
		try {
			InputStream in = new BufferedInputStream(new FileInputStream(FILE_PATH));
			Properties p = new Properties();
			p.load(in);
			if(p != null) {
				url = p.getProperty("url");
				username = p.getProperty("username");
				password = p.getProperty("password");
			}
			in.close();
			Class.forName("org.postgresql.Driver");
		} catch (IOException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		conn = DriverManager.getConnection(url, username, password);
		stmt = conn.createStatement();
	}
	public static void Folders(){
		try {
			File f2=new File("");
			dclj = f2.getCanonicalPath()+"/"+timed;
			System.out.println("导出路径为："+dclj);
			File f=new File(dclj);
			f.mkdirs();
			String os = System.getProperty ("os.name");
			System.out.println("当前系统为："+os);
			 if(os.contains("Windows")) {
			 }else {
				 System.out.println("赋权");
				 Runtime.getRuntime().exec("chmod 777 -R " + dclj);
			 }
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	public static void getExport() {
		try {
			getDao();
			String sql="select relname from pg_stat_user_tables where schemaname='"+timed+"'";
			rs = stmt.executeQuery(sql);
			String sql2="";
			while(rs.next()) {
				sql2+= "copy(SELECT * FROM "+moshitime+"\""+rs.getString(1)+"\") to '"+dclj+"/"+rs.getString(1)+".csv'" + 
						"(FORMAT 'csv',  HEADER true,QUOTE '\"',DELIMITER ',',encoding 'utf8');";
			}
			ps = conn.prepareStatement(sql2);
			ps.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
				try {
					if(ps != null) {
						ps.close();
					}
					if(rs != null) {
						rs.close();
					}
					if(stmt != null) {
						stmt.close();
					}
					if(conn != null) {
						conn.close();
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}
		System.out.println("导出成功！");
	}
	public static PrintStream old = System.out;
	public static void getLog() {
		System.out.println("开启日志功能！");
		try {
			String lj = "conn//rizhi"+timed+".txt";
			File f=new File(lj);
			f.setWritable(true, false);    //设置写权限，windows下不用此语句`
			f.createNewFile();
			FileOutputStream fileOutputStream = new FileOutputStream(f);
			PrintStream printStream = new PrintStream(fileOutputStream);
			System.setOut(printStream);
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
}
 